package com.qfedu.mtl.dao.impl;

import com.qfedu.mtl.dao.MenusDao;
import com.qfedu.mtl.entity.Menus;
import com.qfedu.mtl.query.MenusQuery;
import com.qfedu.mtl.util.DBUtil;
import com.qfedu.mtl.util.StrUtil;
import com.qfedu.mtl.util.handler.impl.BeanListHandler;
import com.qfedu.mtl.util.handler.impl.NumberHandler;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @version v1.0
 * @Date 2023/9/28 11:59
 * @Author DARKER
 * @Email 1402216045@qq.com
 */
public class MenusDaoImpl implements MenusDao {

    /**
     * 分页查询
     * @param page
     * @param size
     * @param menusQuery
     * @return
     */
    @Override
    public List<Menus> selectAll(Integer page, Integer size, MenusQuery menusQuery) {
        List<Object> values = new ArrayList<>();
        StringBuilder sql =new StringBuilder( "select * from tb_menus where 1=1 ");
        if (!StrUtil.isNullOrEmpty(menusQuery.getMenuName())){
            sql.append(" and menu_name like concat('%',?,'%')");
            values.add(menusQuery.getMenuName());
        }
        if (!StrUtil.isNullOrEmpty(menusQuery.getMenuCode())){
            sql.append(" and menu_code like concat('%',?,'%')");
            values.add(menusQuery.getMenuCode());
        }
        if (!StrUtil.isNullOrEmpty(menusQuery.getParentMenuCode())){
            sql.append(" and parent_menu_code like concat('%',?,'%')");
            values.add(menusQuery.getParentMenuCode());
        }
        if (menusQuery.getMenuLevel()!=null){
            sql.append(" and menu_level=? ");
            values.add(menusQuery.getMenuLevel());
        }
        if (menusQuery.getMenuOrder()!=null){
            sql.append(" and menu_order=? ");
            values.add(menusQuery.getMenuOrder());
        }

        if (menusQuery.getMenuState()!=null){
            sql.append(" and menu_state=? ");
            values.add(menusQuery.getMenuState());
        }

        sql.append(" limit ?,?");
        values.add((page-1)*size);
        values.add(size);

        return DBUtil.executeDQL(sql.toString(),DBUtil.getConn(),new BeanListHandler<>(Menus.class,true),values.toArray());
    }
    @Override
    public Integer selectTotal(MenusQuery menusQuery) {
        List<Object> values = new ArrayList<>();
        StringBuilder sql =new StringBuilder( "select count(*) from tb_menus where 1=1 ");
        if (!StrUtil.isNullOrEmpty(menusQuery.getMenuName())){
            sql.append(" and menu_name like concat('%',?,'%')");
            values.add(menusQuery.getMenuName());
        }
        if (!StrUtil.isNullOrEmpty(menusQuery.getMenuCode())){
            sql.append(" and menu_name like concat('%',?,'%')");
            values.add(menusQuery.getMenuCode());
        }
        if (!StrUtil.isNullOrEmpty(menusQuery.getParentMenuCode())){
            sql.append(" and menu_name like concat('%',?,'%')");
            values.add(menusQuery.getParentMenuCode());
        }
        if (menusQuery.getMenuLevel()!=null){
            sql.append(" and menu_level=? ");
            values.add(menusQuery.getMenuLevel());
        }
        if (menusQuery.getMenuOrder()!=null){
            sql.append(" and menu_order=? ");
            values.add(menusQuery.getMenuOrder());
        }

        if (menusQuery.getMenuState()!=null){
            sql.append(" and menu_state=? ");
            values.add(menusQuery.getMenuState());
        }

        return DBUtil.executeDQL(sql.toString(),DBUtil.getConn(),new NumberHandler<>(Long.class),values.toArray()).intValue();
    }

    @Override
    public List<Integer> selectLevel() {
        List<Integer> levelList = new ArrayList<>();
        String sql = "select distinct menu_level from tb_menus";
        try {
            Connection conn = DBUtil.getConn();
            ResultSet resultSet = conn.prepareStatement(sql).executeQuery();
            while (resultSet.next()){
                levelList.add(resultSet.getInt("menu_level"));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return levelList;
    }

    @Override
    public List<String> selectParentMenuCode() {
        List<String> parentMenuCode = new ArrayList<>();
        String sql = "select distinct parent_menu_code from tb_menus";
        try {
            Connection conn = DBUtil.getConn();
            ResultSet resultSet = conn.prepareStatement(sql).executeQuery();
            while (resultSet.next()){
                parentMenuCode.add(resultSet.getString("parent_menu_code"));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return parentMenuCode;
    }

    @Override
    public void delBatchById(List<Integer> ids) {
        StringBuilder sqlBuilder = new StringBuilder("delete from tb_menus where menu_id=? ");
        //批量拼接要删除的id
        for (int i = 0; i < ids.size()-1; i++) {
            sqlBuilder.append(" or menu_id=? ");
        }

        DBUtil.executeDML(sqlBuilder.toString(),DBUtil.getConn(),ids.toArray());
    }
}
